﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Estimate.Common.DBFieldTypes;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 排班表入库记录
    /// </summary>
    public class ScheduleLogRepository : BaseRepository
    {
        public ScheduleLogRepository(string connectionString)
            : base(connectionString,"ScheduleLog")
        { 
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}] (id bigint identity(1,1), type varchar(8), station char(5), realdate date, number int, importtime datetime, filepath varchar(100)) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (type, realdate,station, number) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            return sqlcreate;
        }

        public void InsertRecord(ScheduleType type, string station, DateTime realdate, int number, string filepath)       
        {
            string sql = String.Format("INSERT INTO [{0}] (type,station,realdate,number,importtime,filepath) values(@type,@station,@realdate,@number,@importtime,@filepath)", _tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@station", station);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-01"));
            param[3] = new SqlParameter("@number", number.ToString());
            param[4] = new SqlParameter("@importtime", DateTime.Now.ToString());
            param[5] = new SqlParameter("@filepath", filepath);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        public bool IsRecordExist(ScheduleType type, string station, DateTime realdate, int number)
        {
            string sql = String.Format("SELECT id FROM [{0}] WHERE type=@type and station=@station and realdate=@realdate and number=@number",_tableName);
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@type", type.ToString());
            param[1] = new SqlParameter("@station", station);
            param[2] = new SqlParameter("@realdate", realdate.ToString("yyyy-MM-01"));
            param[3] = new SqlParameter("@number", number.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        public void DeleteRecordWithDateRange( DateTime beginDate, DateTime endDate )
        {
            string sql = String.Format("DELETE FROM [{0}] WHERE realdate >= '{1}' and realdate <='{2}'",
               new object[]{
                    _tableName,
                    beginDate.ToString("yyyy-MM-dd"),
                    endDate.ToString("yyyy-MM-dd")
                });
            DBHelper.ExecuteSql(_connectionString, sql);
        }

        /// <summary>
        /// 给定，年、月。返回已经存在的站点的code
        /// </summary>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        /// <returns>已经存在的站点的名称</returns>
        public string[] GetExistStations(int year, int month)
        {
            string sql = String.Format("SELECT distinct station   FROM [{0}]   WHERE YEAR(realdate) = @year and MONTH(realdate) = @month", _tableName);
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@year", year.ToString());
            parameters[1] = new SqlParameter("@month", month.ToString());

            List<string> result = new List<string>();

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, parameters);

            while (reader.Read())
            {
                result.Add(reader["station"].ToString());
            }
            connection.Close();
            return result.ToArray();
        }

        /// <summary>
        /// 给定时间段，返回已经存在排班表的站点
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public  List<string> GetExistStations(DateTime beginDate, DateTime endDate)
        {
            string sql = String.Format("SELECT distinct station   FROM [{0}]   WHERE realdate>= @beginDate and realdate<= @endDate", _tableName);
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@beginDate", beginDate.ToString());
            parameters[1] = new SqlParameter("@endDate", endDate.ToString());

            List<string> result = new List<string>();

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, parameters);

            while (reader.Read())
                result.Add(reader["station"].ToString());

            connection.Close();
            return result;
        }

    }
}
